
package com.scalar.chiptrack.operations.process.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

import com.scalar.ScalarException;
import com.scalar.chiptrack.db.DBTransaction;
import com.scalar.chiptrack.db.DataSource;
import com.scalar.chiptrack.operations.process.forms.TestForm;
import com.scalar.chiptrack.utils.SqlQueryMappings;

public class TestDAO {


    private TestDAO() {
    }

    public static TestForm loadTestProcessData (int processStepId) throws ScalarException {
        
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rset = null;
        
        TestForm testData = null;
        
        try {
            conn = DataSource.getConnection();

            pstmt = conn.prepareStatement (SqlQueryMappings.get ("operations.test.loadTestData"));
            pstmt.setInt (1, processStepId);
            
            rset = pstmt.executeQuery();

            if (rset.next()) {
                testData = new TestForm (rset.getFloat  ("FTT_Lot_Hold_Yield_Limit"),
                                          rset.getFloat  ("FTT_Lot_Scrap_Yield_Limit"),
                                          rset.getFloat  ("FTT_Target_Yield"),
                                          rset.getFloat  ("FTT_Standard_Unit_Cost"),
                                          rset.getString ("FTT_PO_Frequency"),
                                          rset.getString ("FTT_Test_Jig"),
                                          rset.getString ("FTT_System"),
                                          rset.getString ("procedureDocNumber"),
                                          rset.getString ("procedureDocFileName"),
                                          rset.getString ("programDocNumber"),
                                          rset.getString ("programDocFileName"),
                                          rset.getString ("Part_Number"),
                                          rset.getInt    ("P_Parent_Product_ID"), 
                                          rset.getString ("PS_Type"), 
                                          rset.getString ("PS_Comments"),
                                          rset.getInt    ("previousStep_ID"));
            }
            if( testData != null  )
            {
				testData.setDupProductName(ProductDao.loadProductDupNames( processStepId ));
            }
        }
        catch (Exception sqle) {
            sqle.printStackTrace();
            throw new ScalarException ("Could not LevelAccess list from database", sqle);
        }
        finally {
            if (rset != null) {
                try { rset.close(); } catch (SQLException ignore) {}
            }
            if (pstmt != null) {
                try { pstmt.close(); } catch (SQLException ignore) {}
            }
            if (conn != null) {
                try { conn.close(); } catch (SQLException ignore) {}
            }
        }

        return testData;
    }
    

    public static void insertFinalTestRow (DBTransaction dbTransaction,
                                               int processStepId,
                                               double lotHoldYieldLimit,
                                               double lotScrapYieldLimit,
                                               double targetYield,
                                               double standardUnitCost,
                                               String poFrequency,
                                               int procedureDocId,
                                               int programDocId,
                                               String testJig,
                                               String system) throws ScalarException {
        Connection conn = null;
        PreparedStatement pstmt = null;
        boolean success = false;

        try {
            
            // Get the connection from regular datasource if its not in a manual transaction
            if (dbTransaction == null) {
                conn = DataSource.getConnection();
            }
            else {
                conn = dbTransaction.getConnection();
            }

            pstmt = conn.prepareStatement (SqlQueryMappings.get ("opearations.test.insertTestRow"));

            pstmt.setInt (1, processStepId);
            pstmt.setDouble (2, lotHoldYieldLimit);
            pstmt.setDouble (3, lotScrapYieldLimit);
            pstmt.setDouble (4, targetYield);
            pstmt.setDouble (5, standardUnitCost);
            pstmt.setString (6, poFrequency);
            pstmt.setInt (7, procedureDocId);
            pstmt.setInt (8, programDocId);
            pstmt.setString (9, testJig);
            pstmt.setString (10, system);
            
            pstmt.executeUpdate();
            success = true;
        }
        catch (Exception sqle) {
            sqle.printStackTrace();
            throw new ScalarException ("Could not insert a row into Final_Test table (preocess stepID = " + processStepId + ")", sqle);
        }
        finally {
            
            // Commit/Rollback here it selef -- if its not in a manual transaction
            if (dbTransaction == null) {
                try {
                    if (conn != null) {
                        if (success == true) {
                            conn.commit();
                        }
                        else {
                            conn.rollback();
                        }
                    }
                }
                catch (SQLException ignore) {}
            }

            if (pstmt != null) {
                try { pstmt.close(); } catch (SQLException ignore){}
            }
            if (conn != null) {
                try { conn.close(); } catch (SQLException ignore){}
            }
        }   
    }

	//Update finalTest added by Raj Vallamsetla


	 public static void updateFinalTest (DBTransaction dbTransaction,
                                               int processStepId,
                                               double lotHoldYieldLimit,
                                               double lotScrapYieldLimit,
                                               double targetYield,
                                               double standardUnitCost,
                                               String poFrequency,
                                               int procedureDocId,
                                               int programDocId,
                                               String testJig,
                                               String system) throws ScalarException {
        Connection conn = null;
        PreparedStatement pstmt = null;
        boolean success = false;

        try {
            
            // Get the connection from regular datasource if its not in a manual transaction
            if (dbTransaction == null) {
                conn = DataSource.getConnection();
            }
            else {
                conn = dbTransaction.getConnection();
            }

            pstmt = conn.prepareStatement (SqlQueryMappings.get ("opearations.test.updateTest"));
          
            pstmt.setDouble (1, lotHoldYieldLimit);
            pstmt.setDouble (2, lotScrapYieldLimit);
            pstmt.setDouble (3, targetYield);
            pstmt.setDouble (4, standardUnitCost);
            pstmt.setString (5, poFrequency);
            pstmt.setInt (6, procedureDocId);
            pstmt.setInt (7, programDocId);
            pstmt.setString (8, testJig);
            pstmt.setString (9, system);
			pstmt.setInt (10, processStepId);
            
            pstmt.executeUpdate();
            success = true;
        }
        catch (Exception sqle) {
            sqle.printStackTrace();
            throw new ScalarException ("Could not Update Final_Test table (preocess stepID = " + processStepId + ")", sqle);
        }
        finally {
            
            // Commit/Rollback here it selef -- if its not in a manual transaction
            if (dbTransaction == null) {
                try {
                    if (conn != null) {
                        if (success == true) {
                            conn.commit();
                        }
                        else {
                            conn.rollback();
                        }
                    }
                }
                catch (SQLException ignore) {}
            }

            if (pstmt != null) {
                try { pstmt.close(); } catch (SQLException ignore){}
            }
            if (conn != null) {
                try { conn.close(); } catch (SQLException ignore){}
            }
        }   
    }
	







}